
drop table jms_dm.dm_cust_target_analysis_sum_day_dt
;

create table jms_dm.dm_cust_target_analysis_sum_day_dt(
  cust_id varchar(200) NULL COMMENT "客户id",
  cust_code varchar(200) NULL COMMENT "客户编码",
  ordersource_code varchar(200) NULL COMMENT "订单来源code",
  staff_code varchar(200) NULL COMMENT "业务员code",
  network_code varchar(200) NULL COMMENT "网点编码",
  stat_date date NULL COMMENT "统计日期",
  cust_name varchar(200) NULL COMMENT "客户名称",
  cust_level varchar(200) NULL COMMENT "客户等级4 A,B+,B,E,F ",
  ordersource_name varchar(200) NULL COMMENT "订单来源",
  cust_category varchar(200) NULL COMMENT "客户类别（1-终端寄件，2-农特专递，3-总部共享，4-代理区共享",
  cust_type varchar(200) NULL COMMENT "客户类型",
  cust_project_name varchar(200) NULL COMMENT "客户项目",
  cust_is_loss varchar(200) NULL COMMENT "是否是流失客户",
  cust_is_active varchar(200) NULL COMMENT "是否是活跃客户",
  cust_is_silent varchar(200) NULL COMMENT "是否是沉默客户",
  cust_is_incr varchar(200) NULL COMMENT "是否是新增客户",
  staff_name varchar(200) NULL COMMENT "业务员",
  network_name varchar(200) NULL COMMENT "网点名称",
  fran_code varchar(200) NULL COMMENT "加盟商code",
  fran_name varchar(200) NULL COMMENT "加盟商name",
  agent_code varchar(200) NULL COMMENT "代理区code",
  agent_name varchar(200) NULL COMMENT "代理区名称",
  order_cnt int(11) NULL COMMENT "订单量",
  cancle_cnt int(11) NULL COMMENT "取消量",
  notcollect_cnt int(11) NULL COMMENT "未揽收量",
  taking_cnt int(11) NULL COMMENT "收件量(业务量)",
  void_cnt int(11) NULL COMMENT "作废量"
) ENGINE=OLAP 
DUPLICATE KEY(cust_id, cust_code, ordersource_code, staff_code, network_code)
COMMENT "客户指标分析汇总表"
PARTITION BY RANGE(stat_date)
(START ("2023-10-01") END ("2023-12-31") EVERY (INTERVAL 1 day))
DISTRIBUTED BY HASH(cust_code) BUCKETS 8 
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-366",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "16",
"in_memory" = "false",
"storage_format" = "V2"
);
